<?php
/**
 * Remote Query Client Class
 * Executes a query on a remote server. (Requires server file)
 *
 * July 04, 2007 - Written by Jeff Williams (Initial Release)
 * July 14, 2007 - Improved error handling
 *
 * @version 1.1
 * @author Jeff L. Williams
 */
class RemoteQuery
{
  // Hard code any of these values if you do
  // not wish to pass them into the object
  private $url = "";
  private $securityKey = "";
  private $server = "";
  private $database = "";
  private $username = "";
  private $password = "";
  private $charset = "";
  /**
   * The duration of a query in microseconds
   *
   * @var float
   */
  public $duration;
  /**
   * The total number of rows returned from the last query
   *
   * @var integer
   */
  public $rowCount = 0;
  /**
   * The last SQL statement executed
   *
   * @var string
   */
  public $lastSQL = '';
  /**
   * Contains the error message returned from the last error
   *
   * @var string
   */
  public $error = '';
  /**
   * CONSTRUCTOR
   *
   * @param string  $url The URL to the Remote Query Server file
   * @param string  $securityKey Security password key (set in the Remote Query Server file)
   * @param string  $database Name of the database
   * @param string  $server Name of the MySQL host server
   * @param string  $username Database user name
   * @param string  $password Database password
   * @param string  $charset Database character set
   */
  public function __construct($url = '', $securityKey = '', $database = '', $server = '', $username = '', $password = '', $charset = '')
  {
    $this->connect($url, $securityKey, $database, $server, $username, $password, $charset);
  }
  /**
   * Sets connection data for the remote database and Remote Query Server
   *
   * @param string  $url The URL to the Remote Query Server file
   * @param string  $securityKey Security password key (set in the Remote Query Server file)
   * @param string  $database Name of the database
   * @param string  $server Name of the MySQL host server
   * @param string  $username Database user name
   * @param string  $password Database password
   * @param string  $charset Database character set
   */
  public function connect($url, $securityKey = '', $database = '', $server = '', $username = '', $password = '', $charset = '')
  {
    // Save any passed in parameters in our local variables
    if (strlen($url) > 0)
      $this->url = $url;
    if (strlen($securityKey) > 0)
      $this->securityKey = $securityKey;
    if (strlen($database) > 0)
      $this->database = $database;
    if (strlen($server) > 0)
      $this->server = $server;
    if (strlen($username) > 0)
      $this->username = $username;
    if (strlen($password) > 0)
      $this->password = $password;
    if (strlen($charset) > 0)
      $this->charset = $charset;
  }
  /**
   * Executes a query on the remote server
   *
   * @param string  $sql SQL query to execute on the remote server
   * @return array  An associative array containing the result set
   */
  public function executeQuery($sql)
  {
    // Retrieve XML data from the remote server
    $xml = file_get_contents($this->getURL($sql));
    // If there was an error getting the data from this URL...
    if (!$xml) {
      // Set the error
      // $this->error = 'Could not connect to URL: ' . $this->url;
      $this->error = 'Could not connect to remote server';
      return false;
    } //!$xml
    else {
      // Check to make sure we got XML back
      if (strtolower(substr($xml, 0, 5)) <> '<?xml') {
        // If not, set the error
        $this->error = $xml;
        return false;
        // Else, success - we have XML data
      } //strtolower(substr($xml, 0, 5)) <> '<?xml'
      else {
        // Parse the XML into an array
        $values = array();
        $index  = array();
        $array  = array();
        $parser = xml_parser_create();
        xml_parser_set_option($parser, XML_OPTION_SKIP_WHITE, 1);
        xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, 0);
        xml_parse_into_struct($parser, $xml, $values, $index);
        xml_parser_free($parser);
        // Set local variables from return data
        $this->rowCount = $values['0']['attributes']['rows'];
        $this->duration = $values['0']['attributes']['microseconds'];
        $this->lastSQL  = $values['0']['attributes']['query'];
        // If there is array data
        if ($this->rowCount > 0) {
          // Clean up the array
          $i            = 0;
          $name         = $values[$i]['tag'];
          $array[$name] = isset($values[$i]['attributes']) ? $values[$i]['attributes'] : '';
          $array[$name] = self::StructureToArray($values, $i);
          // No error occured
          $this->error  = '';
          // Return the part of the array we need
          return $array['root']['row'];
          // Else, we have no array data
        } //$this->rowCount > 0
        else {
          // Return an empty array
          return array();
        }
      }
    }
  }
  /**
   * Creates the full URL with GET parameters to execute a query on the server
   *
   * @param string  $sql SQL query to execute
   * @return string Full URL to return XML result set from remote server
   */
  public function getURL($sql = '')
  {
    // Add the GET parameters
    $url = '';
    if (strlen($this->securityKey) > 0)
      $url .= '&key=' . urlencode($this->securityKey);
    if (strlen($this->database) > 0)
      $url .= '&db=' . urlencode($this->database);
    if (strlen($this->server) > 0)
      $url .= '&host=' . urlencode($this->server);
    if (strlen($this->username) > 0)
      $url .= '&user=' . urlencode($this->username);
    if (strlen($this->password) > 0)
      $url .= '&pass=' . urlencode($this->password);
    if (strlen($this->charset) > 0)
      $url .= '&charset=' . urlencode($this->charset);
    if (strlen($sql) > 0)
      $url .= '&sql=' . urlencode($sql);
    // Append these parameters onto the default URL
    if (strlen($url) > 0) {
      $url = $this->url . "?" . substr($url, 1);
    } //strlen($url) > 0
    else {
      $url = $this->url;
    }
    // Return the new query URL
    return $url;
  }
  /**
   * StructureToArray($array, $index)
   *
   * This is adds the contents of the returned xml into the array (recursive)
   *
   * @access private
   * @param array  $array this is the xml data in an array
   * @param int $index this is the current location in the array
   * @return array
   */
  private static function StructureToArray($array, &$index)
  {
    $child = array();
    if (isset($array[$index]['value']))
      array_push($child, $array[$index]['value']);
    while ($index++ < count($array)) {
      switch ($array[$index]['type']) {
        case 'cdata':
          array_push($child, $array[$index]['value']);
          break;
        case 'complete':
          $name = $array[$index]['tag'];
          if (strlen($name) > 0) {
            $child[$name] = ($array[$index]['value']) ? ($array[$index]['value']) : '';
            if (isset($array[$index]['attributes'])) {
              $child[$name] = $array[$index]['attributes'];
            } //isset($array[$index]['attributes'])
          } //strlen($name) > 0
          break;
        case 'open':
          $name                = $array[$index]['tag'];
          $size                = isset($child[$name]) ? sizeof($child[$name]) : 0;
          $child[$name][$size] = self::StructureToArray($array, $index);
          break;
        case 'close':
          return $child;
          break;
      } //$array[$index]['type']
    } //$index++ < count($array)
    return $child;
  }
}
?>
